﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using NodeSource.Executor;
using NodeSource.Result;

namespace NodeSource.DataProvider.Sql
{
    public class SqlDataProvider : IDataProvider
    {
        private readonly SqlConnection _sqlConnection;

        #region IDataProvider Members

        public SqlDataProvider(Connection connection)
        {
            _sqlConnection = new SqlConnection(connection.ConnectionResolver);
        }

        public DataProviderType DataProviderType
        {
            get { return DataProviderType.Sql; }
        }

        public bool Open()
        {
            try
            {
                _sqlConnection.Open();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public bool Close()
        {
            try
            {
                _sqlConnection.Close();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public bool IsActive()
        {
            return _sqlConnection.State == ConnectionState.Open;
        }

        public ResultSet Query(Query query)
        {
            var command = new SqlCommand(ParseQuery(query), _sqlConnection);
            var adapter = new SqlDataAdapter(command);

            var resultSet = new ResultSet { IsSuccess = false };
            try
            {
                var dataSet = new DataSet();
                adapter.Fill(dataSet);

                foreach (DataTable table in dataSet.Tables)
                {
                    var resultTable = new Table { Name = table.TableName };

                    foreach (DataColumn column in table.Columns)
                    {
                        var resultColumn = new Column { Name = column.ColumnName, FieldType = FieldType.Varchar };
                        resultTable.Columns.Add(resultColumn);
                    }

                    foreach (DataRow row in table.Rows)
                    {
                        var resultRow = resultTable.NewRow();
                        foreach (DataColumn column in table.Columns)
                        {
                            resultRow.SetValue(column.ColumnName, row[column.ColumnName]);
                        }
                    }

                    resultSet.Tables.Add(resultTable);
                }
                resultSet.IsSuccess = true;
            }
            catch (Exception ex)
            {
            }

            return resultSet;
        }

        private static String ParseQuery(Query query)
        {
            if (query is WhereQuery)
            {
                var queryExpr = query as WhereQuery;

                if (queryExpr.Predicate == null)
                    return ParseQuery(queryExpr.Query);

                return String.Format("{0} where {1}", ParseQuery(queryExpr.Query), queryExpr.Predicate.Expression);
            }
            else if (query is OrderQuery)
            {
                var queryExpr = query as OrderQuery;

                if (queryExpr.Order == null)
                    return ParseQuery(queryExpr.Query);

                return String.Format("{0} order by {1} {2}", ParseQuery(queryExpr.Query), queryExpr.Order.OnColumn, queryExpr.Order.OrderType);
            }
            else if (query is SelectQuery)
            {
                var queryExpr = query as SelectQuery;

                var selectClause = queryExpr.SelectAction.Data.Aggregate("", (current, data) => current + data.ToString() + ",");
                selectClause = selectClause.Length == 1 ? "*" : selectClause.Substring(0, selectClause.Length - 1);

                return String.Format("select {1} from {0}", ParseQuery(queryExpr.Query), selectClause);
            }
            else if (query is FromQuery)
            {
                var queryExpr = query as FromQuery;

                if (queryExpr.QuerySource.QuerySourceType == QuerySourceType.Node)
                {
                    var source = queryExpr.QuerySource.GetSource() as NodeSource;
                    return String.Format("{0} {1}", source.Name, source.Alias);
                }
                else
                {
                    var source = queryExpr.QuerySource.GetSource() as Query;
                    return String.Format("({0}) {1}", ParseQuery(source), queryExpr.QuerySource.Alias);
                }
                
            }
            else if (query is JoinQuery)
            {
                var queryExpr = query as JoinQuery;

                if (queryExpr.TargetSource == null)
                    return ParseQuery(queryExpr.Query);

                String secondSource = "";
                if (queryExpr.TargetSource.QuerySourceType == QuerySourceType.Node)
                {
                    var source = queryExpr.TargetSource.GetSource() as NodeSource;
                    secondSource = String.Format("{0} {1}", source.Name, source.Alias);
                }
                else
                    secondSource = ParseQuery(queryExpr.TargetSource.GetSource() as Query);

                switch(queryExpr.JoinClause)
                {
                    case JoinType.Inner:
                        return String.Format("{0} inner join {1}", ParseQuery(queryExpr.Query), secondSource);
                    case JoinType.Left:
                        return String.Format("{0} left outer join {1}", ParseQuery(queryExpr.Query), secondSource);
                    case JoinType.Right:
                        return String.Format("{0} right outer join {1}", ParseQuery(queryExpr.Query), secondSource);
                    default:
                        return String.Format("{0}, {1}", ParseQuery(queryExpr.Query), secondSource);
                }
            }
            else if (query is OnQuery)
            {
                var queryExpr = query as OnQuery;

                if (queryExpr.Predicate == null)
                    return ParseQuery(queryExpr.Query);

                return String.Format("{0} on {1}", ParseQuery(queryExpr.Query), queryExpr.Predicate.Expression);
            }

            return String.Empty;
        }

        #endregion

        #region IDisposable Members

        public void Dispose()
        {
            throw new NotImplementedException();
        }

        #endregion


        public bool CanBeQueriedInGroup
        {
            get { return true; }
        }
    }
}